﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.IO;
using System.Data.Common;

namespace MISAPP.Common
{
    /// <summary>
    /// 和資料庫溝通相關的程式在這
    /// </summary>
    public class DAL3
    {
        protected static Dictionary<string, DbProviderFactory> factory = null;
        protected static DbConnection _connection = null;
        protected static string connectionString = string.Empty;
        protected static Dictionary<string, DbConnection> connectionDict = null;
        protected static DbConnection connection
        {
            get
            {
                if (factory == null)
                {
                    factory = new Dictionary<string, DbProviderFactory>
                        {
                            {CurrentOpt
                            ,DbProviderFactories.GetFactory(getDBProvider())}
                        };
                }

                if (_connection == null)
                {
                    DbProviderFactory _factory = getFactory();
                    _connection = _factory.CreateConnection();
                    _connection.ConnectionString = connectionString;
                    connectionDict = new Dictionary<string, DbConnection>
                    {
                        {CurrentOpt,_connection}
                    };
                }
                else
                {
                    _connection = getConnection();
                }
                return _connection;
            }
        }
          
        protected static string currentOpt = "MSSQL";

        public static string CurrentOpt
        {
            set
            {
                currentOpt = string.Empty.Equals(value) 
                    ? currentOpt : value;
            }
            get
            {
                return currentOpt;
            }
        }

        protected static string getDBProvider()
        {
            string configFile = "Common.dll";
            Configuration config = null;
            if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + configFile)) //如果這一層沒有，就再加bin
            {
                config = ConfigurationManager.OpenExeConfiguration(configFile);
            }
            else
            {
                config = ConfigurationManager.OpenExeConfiguration(AppDomain.CurrentDomain.BaseDirectory + @"bin\" + configFile);
            }
            connectionString = config.ConnectionStrings
                .ConnectionStrings[CurrentOpt].ToString();
            return config.ConnectionStrings
                .ConnectionStrings[CurrentOpt].ProviderName.ToString();
        }

        public static DbCommand GetCommand()
        {            
            return connection.CreateCommand();
        }

        public static DbCommand GetCommand(string p_opt)
        {
            CurrentOpt = p_opt;
            return GetCommand();
        }

        public static IDataReader GetDataReader(IDbCommand p_cmd)
        {
            IDataReader reader = null;
            try
            {
                connectionOpen();
                reader = p_cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Data.Common.DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {
                // Connection.Close();
            }
            return reader;
        }

        public static IDataReader GetDataReader(IDbCommand p_cmd,string p_opt)
        {
            CurrentOpt = p_opt;
            return GetDataReader(p_cmd);
        }

        public static DataTable GetDataTable(IDbCommand p_cmd,string p_opt)
        {
            CurrentOpt = p_opt;
            return GetDataTable(p_cmd);
        }

        public static DataSet GetDataSet(DbCommand p_cmd,string p_opt)
        {
            CurrentOpt = p_opt;
            return GetDataSet(p_cmd);
        }

        public static DataSet GetDataSet(DbCommand p_cmd)
        {
            DataSet ds = new DataSet();
            try
            {
                connectionOpen();
                DbDataAdapter da = getDataAdapter(p_cmd);
                da.Fill(ds);
            }
            catch (System.Data.Common.DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {
                //ConnectionOpenClose();
                //Connection.Close();
            }
            return ds;
        }

        protected static DbDataAdapter getDataAdapter(DbCommand p_cmd)
        {
            DbProviderFactory _factory = getFactory();
            DbDataAdapter da = _factory.CreateDataAdapter();
            da.SelectCommand = p_cmd;
            return da;
        }

        protected static DbProviderFactory getFactory()
        {
            DbProviderFactory _factory = null;
            if (!factory.TryGetValue(CurrentOpt, out _factory))
            {
                _factory = DbProviderFactories.GetFactory(getDBProvider());
                factory.Add(CurrentOpt, _factory);
            }
            return _factory;        
        }

        protected static DbConnection getConnection()
        {
            DbConnection _con = null;
            DbProviderFactory _factory = null;
            if (!connectionDict.TryGetValue(CurrentOpt, out _con))
            {
                _factory = getFactory();
                _con = _factory.CreateConnection();
                _con.ConnectionString = connectionString;
                connectionDict.Add(CurrentOpt, _con);
            }
            return _con;
        }


        public static DataTable GetDataTable(IDbCommand p_cmd)
        {
            DataTable dt = new DataTable();
            try
            {
                dt.Load(GetDataReader(p_cmd));
            }
            catch (System.Data.Common.DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {

            }
            return dt;
        }

        /// <summary>
        /// 回傳 IDbDataParameter
        /// </summary>
        /// <param name="p_name">欄位名</param>
        /// <param name="p_dbType">欄位型態</param>
        /// <param name="p_value">值</param>
        /// <returns>IDbDataParameter</returns>
        public static DbParameter GetParameter(string p_name, DbType p_dbType, object p_value, DbParameter p_para)
        {
            p_para.ParameterName = p_name;
            p_para.DbType = p_dbType;
            p_para.Value = p_value;
            return p_para;
        }

        /// <summary>
        /// 要傳入的 Command 要先把Parameter 組好
        /// </summary>
        /// <param name="p_cmd">IDBCommand</param>
        /// <returns>int</returns>
        public static int ExecuteNoneQuery(DbCommand p_cmd)
        {
            int iResult = -1;
            try
            {
                connectionOpen();
                iResult = p_cmd.ExecuteNonQuery();
            }
            catch (DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {
                connection.Close();
            }
            return iResult;
        }

        /// <summary>
        /// 如果沒有打開，那就開了，如果已開了就不用開了^^
        /// </summary>
        protected static void connectionOpen()
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
        }


        protected static void exceptionHandle(Exception p_ex)
        {
            throw new Exception(p_ex.Message);
        }

        public static void setTest(string p_opt)
        {
            CurrentOpt = p_opt;
        }

        public static void setTest()
        { }
    }
}


//System.Data.OracleClient.OracleConnection cn = new System.Data.OracleClient.OracleConnection();
//            cn.ConnectionString = "Data Source=FUBON_ODSDB;User ID=T_SAINTCHANG;Password=1qaz2wsx;";
//            cn.Open();
//            System.Data.OracleClient.OracleCommand cmd = cn.CreateCommand();
//            cmd.CommandText = "SELECT SYSDATE FROM DUAL";
//            System.Data.OracleClient.OracleDataReader dr = cmd.ExecuteReader();
//            System.Data.DataTable dt = new System.Data.DataTable();
//            dt.Load(dr);